<?php
class Master_siswa extends CI_Model 
{

	function __construct()
	{
		parent::__construct();
	}
	
	function add($dataInsert)
	{
		$query = $this->db->insert('mr_siswa', $dataInsert);
		return $query;
	}
		
	function read()
	{			
		$query=$this->db->get('mr_siswa');
		return $query->result();
	}	
	
	function edit($nis)
	{
		$this->db->where('nis',$nis);
		$query=$this->db->get('mr_siswa');
		return $query->result();
	}
	
	function update($nis,$dataInsert)
	{
		$this->db->where('nis', $nis);
		$update = $this->db->update('mr_siswa', $dataInsert);
		return $update;
	}
	
	function delete($nis)
	{
		$this->db->where('nis', $nis);
		$delete = $this->db->delete('mr_siswa');
		return $delete;
	}
	
	function detail($nis){
		$sql="select a.*,b.th_ajaran from mr_siswa a left join mr_th_ajaran b on a.id_tapel_masuk=b.id_mr_th_ajaran 
				where a.nis='".$nis."'"; 
		//echo $sql;
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result();
		}else{
			return array();
		}
	}
	
	function cek_nis($nis){
		$id_lembaga=$this->session->userdata('id_lembaga');
		$sql="select *from mr_siswa where nis='".$nis."'";
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return true;
		}else{
			return false;
		}
	}
	
	function cek_nis_bayar($nis){
		$sql="select *from mr_siswa where nis='".$nis."'";
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return true;
		}else{
			return false;
		}
	}
	
	function get_detail_wali($nis){
		$sql="SELECT * FROM mr_ortuwali WHERE nis='".$nis."' and tlp <> '' ORDER BY FIELD( hubungan, 'Ibu', 'Ayah', 'Wali' )";
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->row_array();
		}else{
			return array();
		}
	}
	
	function get_all_data(){
		$sql="select * from mr_siswa";
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result_array();
		}else{
			return array();
		}
	}
	
	
	function get_lembaga(){
		$sql="select * from mr_lembaga";
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result_array();
		}else{
			return array();
		}
	}
	
	function get_data_by_kelas($id_lembaga,$kelas,$id_tapel){
		if($id_lembaga=="all"){
			$filter_kelas="";
		}else{
			if($kelas=="all"){
				$filter_kelas=" and id_lembaga='".$id_lembaga."'";
			}else{
				$filter_kelas=" and nis in 
				(select nis from bagi_kelas a left join atur_kelas b on a.id_atur_kelas=b.id_atur_kelas 
					where id_lembaga='".$id_lembaga."' and id_mr_th_ajaran='".$id_tapel."' and id_mr_kelas='".$kelas."')";
			}
		}
		
		$sql="select * from (SELECT *,(SELECT mk.kelas FROM bagi_kelas bk LEFT JOIN atur_kelas ak ON bk.id_atur_kelas=ak.id_atur_kelas
	LEFT JOIN mr_kelas mk ON ak.id_mr_kelas=mk.id_mr_kelas WHERE s.nis=bk.nis AND ak.id_mr_th_ajaran='".$id_tapel."' LIMIT 1)AS nama_kelas FROM mr_siswa s
 where 1=1 $filter_kelas) as data_raw order by nama_kelas, nis asc";
 //echo $sql;
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result_array();
		}else{
			return array();
		}
	}

	function limit($limit,$offset)
	{			
		$query=$this->db->get('mr_siswa',$limit,$offset);
		return $query->result();
	}

	function num_rows()
	{	
		$query = $this->db->get('mr_siswa');
		return $query->num_rows();
	}

	function limit_search($id_lembaga,$keyword,$limit,$offset){
		if($id_lembaga=="all"){
			$filter_lembaga="";
		}else{
			$filter_lembaga=" and id_lembaga='".$id_lembaga."'";
		}
		$sql="select * from mr_siswa where (nis like '%".$keyword."%' or nm_lengkap like '%".$keyword."%') $filter_lembaga
			limit $offset,$limit";
		//echo $sql;
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result();
		}else{
			return array();
		}
		//$this->db->where('id_lembaga',$id_lembaga);
		//$this->db->like('nm_lengkap',$keyword);	
		//$this->db->or_like('nis',$keyword);
		//$query=$this->db->get('mr_siswa',$limit,$offset);
		//return $query->result();
	}

	function num_rows_search($id_lembaga,$keyword){	
		if($id_lembaga=="all"){
			$filter_lembaga="";
		}else{
			$filter_lembaga=" and id_lembaga='".$id_lembaga."'";
		}
		$sql="select * from mr_siswa where (nis like '%".$keyword."%' or nm_lengkap like '%".$keyword."%') $filter_lembaga";
		//echo $sql;
		$query=$this->db->query($sql);
		//$this->db->where('id_lembaga',$id_lembaga);
		//$this->db->like('nm_lengkap',$keyword);
		//$this->db->or_like('nis',$keyword);
		//$query = $this->db->get('mr_siswa');
		return $query->num_rows();
	}
	
	function nis($kode)
	{	
		$sql="select max(right(nis,4)) as nis from mr_siswa where nis like '$kode%'";
		$query=$this->db->query($sql);
		return $query->row_array();
	}
	
	function detail_kelas($nis,$id_tapel){
		$sql="SELECT
					`atur_kelas`.`id_mr_th_ajaran`
					, `mr_siswa`.`nm_lengkap`
					, `mr_siswa`.`nis`
					, `mr_lembaga`.`nama_lembaga`
					, `mr_kelas`.`kelas`
				FROM
					`bagi_kelas`
					left JOIN `atur_kelas` 
						ON (`bagi_kelas`.`id_atur_kelas` = `atur_kelas`.`id_atur_kelas`)
					left JOIN `mr_siswa` 
						ON (`bagi_kelas`.`nis` = `mr_siswa`.`nis`)
					left JOIN `mr_lembaga` 
						ON (`mr_siswa`.`id_lembaga` = `mr_lembaga`.`id_lembaga`)
					left JOIN `mr_kelas` 
						ON (`atur_kelas`.`id_mr_kelas` = `mr_kelas`.`id_mr_kelas`)
				WHERE (`atur_kelas`.`id_mr_th_ajaran` =".$id_tapel."
					AND `mr_siswa`.`nis` ='".$nis."');";
	$query=$this->db->query($sql);
		return $query->row_array();
	}
}